package cn.stylefeng.guns.sys.modular.dbcontentapproval.service.impl;

import cn.hutool.db.Db;
import cn.stylefeng.guns.base.auth.context.LoginContextHolder;
import cn.stylefeng.guns.base.auth.model.LoginUser;
import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.sys.modular.dbList.model.params.DblistParam;
import cn.stylefeng.guns.sys.modular.dbList.model.result.DblistResult;
import cn.stylefeng.guns.sys.modular.dbList.service.DblistService;
import cn.stylefeng.guns.sys.modular.dbServicelist.entity.DbServicelist;
import cn.stylefeng.guns.sys.modular.dbServicelist.model.params.DbServicelistParam;
import cn.stylefeng.guns.sys.modular.dbServicelist.model.result.DbServicelistResult;
import cn.stylefeng.guns.sys.modular.dbServicelist.service.DbServicelistService;
import cn.stylefeng.guns.sys.modular.dbcontentapproval.entity.DbContentapproval;
import cn.stylefeng.guns.sys.modular.dbcontentapproval.entity.EtlJob;
import cn.stylefeng.guns.sys.modular.dbcontentapproval.mapper.DbContentapprovalMapper;
import cn.stylefeng.guns.sys.modular.dbcontentapproval.model.DbDatabase;
import cn.stylefeng.guns.sys.modular.dbcontentapproval.model.DbTable;
import cn.stylefeng.guns.sys.modular.dbcontentapproval.model.DbTableDetail;
import cn.stylefeng.guns.sys.modular.dbcontentapproval.model.GetYmlData;
import cn.stylefeng.guns.sys.modular.dbcontentapproval.model.params.DbContentapprovalParam;
import cn.stylefeng.guns.sys.modular.dbcontentapproval.model.result.DbContentapprovalResult;
import cn.stylefeng.guns.sys.modular.dbcontentapproval.service.DbContentapprovalService;
import cn.stylefeng.guns.sys.modular.dbservicedetails.model.params.DbServicedetailsParam;
import cn.stylefeng.guns.sys.modular.dbservicedetails.model.result.DbServicedetailsResult;
import cn.stylefeng.guns.sys.modular.dbservicedetails.service.DbServicedetailsService;
import cn.stylefeng.guns.sys.modular.metadata.model.params.MetadataParam;
import cn.stylefeng.guns.sys.modular.metadata.model.result.MetadataResult;
import cn.stylefeng.guns.sys.modular.metadata.service.MetadataService;
import cn.stylefeng.guns.sys.modular.notice.entity.DbNotice;
import cn.stylefeng.guns.sys.modular.notice.model.params.DbNoticeParam;
import cn.stylefeng.guns.sys.modular.notice.service.DbNoticeService;
import cn.stylefeng.roses.core.util.ToolUtil;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import jdk.nashorn.internal.objects.annotations.Getter;
import org.apache.http.HttpEntity;
import org.apache.http.client.ClientProtocolException;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.CloseableHttpResponse;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClients;
import org.apache.http.message.BasicNameValuePair;
import org.apache.http.util.EntityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.security.auth.login.LoginContext;
import java.io.IOException;
import java.io.Serializable;
import java.io.UnsupportedEncodingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Random;

/**
 * <p>
 * 内容审核列表  服务实现类
 * </p>
 *
 * @author zhaoyiheng
 * @since 2020-08-20
 */
@Service
@Component
public class DbContentapprovalServiceImpl extends ServiceImpl<DbContentapprovalMapper, DbContentapproval> implements DbContentapprovalService {


    @Autowired
    private DbServicelistService dbServicelistService;
    @Autowired
    private DbNoticeService dbNoticeService;
    @Autowired
    private DbServicedetailsService dbServicedetailsService;
    @Autowired
    private DblistService dblistService;
    @Autowired
    private MetadataService metadataService;
    @Autowired
    private GetYmlData getYmlData;

    @Override
    public void add(DbContentapprovalParam param) {
        DbContentapproval entity = getEntity(param);
        this.save(entity);
    }

    @Override
    public void delete(DbContentapprovalParam param) {
        this.removeById(getKey(param));
    }

    @Override
    public void update(DbContentapprovalParam param) {
        DbContentapproval oldEntity = getOldEntity(param);
        DbContentapproval newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        this.updateById(newEntity);
    }

    @Override
    public DbContentapprovalResult findBySpec(DbContentapprovalParam param) {
        return null;
    }

    @Override
    public List<DbContentapprovalResult> findListBySpec(DbContentapprovalParam param) {
        return null;
    }

    @Override
    public LayuiPageInfo findPageBySpec(DbContentapprovalParam param) {
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param);
        return LayuiPageFactory.createPageInfo(page);
    }


    @Override
    public void onApprove(DbContentapprovalParam dbContentapprovalParam) {
        DbContentapproval dbContentapproval = new DbContentapproval();
        dbContentapproval.setId(dbContentapprovalParam.getId());
        dbContentapproval.setState("1");
        dbContentapproval.setUpdatedTime(new Date());
        dbContentapproval.setUpdatedBy(LoginContextHolder.getContext().getUser().getUsername());
        baseMapper.updateById(dbContentapproval);
        DbServicelist dbServicelist = new DbServicelist();
        dbServicelist.setId(dbContentapprovalParam.getServiceid());
        dbServicelist.setSpState("1");
        dbServicelist.setUpdatedBy(LoginContextHolder.getContext().getUser().getUsername());
        dbServicelist.setUpdatedTime(new Date());


//        DbServicelistParam dbServicelistParam1 = new DbServicelistParam();
//        dbServicelistParam1.setId(dbContentapprovalParam.getServiceid());
//        dbServicelistParam1.setSpState("1");
//        dbServicelistParam1.setUpdatedBy(LoginContextHolder.getContext().getUser().getUsername());
//        dbServicelistParam1.setUpdatedTime(new Date());
//        dbServicelistService.update(dbServicelistParam1);


        Integer id = dbContentapprovalParam.getServiceid();
        //获取到要审批的申请数据res
        DbServicelistResult res = dbServicelistService.queryDbServiceListInfo(id);


        //审批通过将管理端数据源服务中数据库状态展示出来
        //1.查询注册的数据库信息
        DbServicelistResult dbServicelistResult = dbServicelistService.queryDbServiceListInfo(dbContentapprovalParam.getServiceid());
        int userId = Math.toIntExact(dbServicelistResult.getUserId());
        //管理端数据库状态
        String DbName = "";
        if (userId == 6) {
            DbName = "sygaj_" + res.getDbName();
        }
        if (userId == 7) {
            DbName = "symzj_" + res.getDbName();
        }
        if (userId == 8) {
            DbName = "syczj_" + res.getDbName();
        }
        if (userId == 9) {
            DbName = "syfcj_" + res.getDbName();
        }
        if (userId == 10) {
            DbName = "syrsj_" + res.getDbName();
        }
        if (userId == 11) {
            DbName = "sycjj_" + res.getDbName();
        }


        //审批通过的时候放在通知中心一条消息更改其中的状态
        DbNoticeParam dbNoticeParam = new DbNoticeParam();
        dbNoticeParam.setSpState("1");
        dbNoticeParam.setState("0");
        dbNoticeParam.setAdminState("0");
        dbNoticeParam.setFirstIn("1");
        dbNoticeParam.setServiceId(dbContentapprovalParam.getServiceid());
        dbNoticeService.updateInfoByServiceId(dbNoticeParam);

        //审批通过之后自动抓取批准的数据库中的表信息
        //TODO 审批通过之后自动抓取批准的数据库中的表信息   oracle数据库操作未完成

        //获取到审批数据库的信息
        String username = res.getUsername();
        String password = res.getPassword();
        String dbType = res.getDbType();
        String driver = "";
        String url = "";
        //mysql数据库和Oracle数据库分别处理
        //声明连接
        Connection conn = null;
        Connection conn1 = null;
        Connection conn3 = null;
        if (dbType.equals("1")) {
            //驱动程序名
            driver = "com.mysql.cj.jdbc.Driver";
            //URL指向要访问的数据库名mydata
            url = "jdbc:mysql://" + res.getDbUrl() + ":" + res.getDbNumber() + "/" + res.getDbName() + "?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL&useSSL=false&serverTimezone=CTT";
        } else if (dbType.equals("2")) {
            //驱动程序名
            driver = "oracle.jdbc.OracleDriver";
            //URL指向要访问的数据库名mydata
            url = "jdbc:oracle:thin:@" + res.getDbUrl() + ":" + res.getDbNumber() + "/" + res.getDbName();
        }
        //遍历查询结果集
        ResultSet rs = null;
        ResultSet rs1 = null;
        ResultSet rs3 = null;
        List<String> tableNames = new ArrayList<>();
        List<String> tableDetails = new ArrayList<>();
        List<Integer> tableAutoIncrement = new ArrayList<>();
        //创建数据库
        //创建数据库之前先获取到当前申请数据库的部门
        //创建完数据库放到表中存起来
//        DbDatabase dbDatabase = new DbDatabase();
        List<DbDatabase> list = new ArrayList<>();
        List<DbTable> list1 = new ArrayList<>();
        //用来保存某个数据库下table的id
        List<Integer> tableId = new ArrayList<>();


        //注册申请
        if ("0".equals(dbContentapprovalParam.getContent())) {


            //创建之前查询是否存在同名数据库，有的话删除
            //创建数据库暂时不用
//            this.baseMapper.deleteDatabase(DbName);
//            this.baseMapper.createTable(DbName);
//        dbDatabase.setDName(res.getDbName());
//        this.baseMapper.insertDbDatabase(dbDatabase);
//        list = this.baseMapper.queryDbDatabase(dbDatabase);

            //2.将其中的数据传入管理端数据库状态表中
            DblistParam dblistParam = new DblistParam();
            dblistParam.setDbUrl(dbServicelistResult.getDbUrl());
            dblistParam.setState("0");
            dblistParam.setSpState("1");
            dblistParam.setServiceListId(dbServicelistResult.getId());
            dblistParam.setDbName(DbName);
            dblistParam.setDbDescribe(dbServicelistResult.getDetails());
            dblistParam.setCreatedBy(dbServicelistResult.getCreatedBy());
            dblistParam.setCreatedTime(dbServicelistResult.getCreatedTime());
            dblistParam.setUpdatedBy(dbServicelistResult.getUpdatedBy());
            dblistParam.setUpdatedTime(dbServicelistResult.getUpdatedTime());
            dblistParam.setDbMemory(dbServicelistResult.getDbMemory());
            dblistService.add(dblistParam);


            try {
                //加载驱动程序
                Class.forName(driver);
                //1.getConnection()方法，连接MySQL数据库！！
                conn = DriverManager.getConnection(url, username, password);
                //执行的sql获取该数据库下所有表
                String sql = "select table_name from information_schema.TABLES  where table_schema = " + "\'" + res.getDbName() + "\'";
                Statement stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                while (rs.next()) {
                    tableNames.add(rs.getString(1));
                }
                //把表放到数据库中


                //获取所有表下的表注释说明
                conn1 = DriverManager.getConnection(url, username, password);
                Statement stmt1 = conn1.createStatement();
                for (int j = 0; j < tableNames.size(); j++) {
                    String sql1 = "SELECT TABLE_COMMENT,AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = " + "\'" + res.getDbName() + "\'" + "AND table_name =" + "\'" + tableNames.get(j) + "\'";
                    rs1 = stmt1.executeQuery(sql1);
                    while (rs1.next()) {
                        tableDetails.add(rs1.getString(1));
                        tableAutoIncrement.add(rs1.getInt(2));
                    }
                }


                //获取到当前数据库下所有表,循环将表保存到该数据库详情表servicedetails下
                DbServicedetailsParam param = new DbServicedetailsParam();
                //批准的用户
                LoginUser user = LoginContextHolder.getContext().getUser();
                for (int i = 0; i < tableNames.size(); i++) {
                    param.setServiceId(res.getId());
                    param.setTableName(tableNames.get(i));
                    param.setDetails(tableDetails.get(i));
                    dbServicedetailsService.add(param, user);
                }

                //获取到的信息保存到admindata数据库中的db_table中
//            DbTable dbTable = new DbTable();
//            for (int i = 0; i < tableNames.size(); i++) {
//                dbTable.setDId(list.get(0).getDId());
//                dbTable.setTName(tableNames.get(i));
//                dbTable.setTDetail(tableDetails.get(i));
//                dbTable.setTAutoIncrement(tableAutoIncrement.get(i));
//                this.baseMapper.insertDbTable(dbTable);
//            }

                //保存之后查出所有表的id
//            DbTable dbTable1 = new DbTable();
//            dbTable1.setDId(list.get(0).getDId());
                //保存所有查询到的table
//            list1 = this.baseMapper.queryDbTable(dbTable1);

                //获取到table的字段详细信息并保存
                //获取到表中字段的详情并保存
//            conn3 = DriverManager.getConnection(url, username, password);
//            Statement stmt2 = conn3.createStatement();
//            DbTableDetail dbTableDetail = new DbTableDetail();
                //循环获取到的table
//            for (int i = 0; i < list1.size(); i++) {
//                int table_id = list1.get(i).getTId();
//                //把tableid都保存起来方便之后查询表字段信息
//                tableId.add(table_id);
//                String sql2 = "select COLUMN_NAME,COLUMN_TYPE,NUMERIC_SCALE,IS_NULLABLE,COLUMN_KEY,COLUMN_COMMENT,CHARACTER_SET_NAME,COLLATION_NAME,EXTRA,COLUMN_DEFAULT  from information_schema.columns where table_schema =" + "\'" + res.getDbName() + "\'" + " and table_name =" + "\'" + list1.get(i).getTName() + "\'";
//                rs3 = stmt2.executeQuery(sql2);
//                //将获取到的表信息保存起来
//                while (rs3.next()) {
//                    dbTableDetail.setTId(table_id);
//                    dbTableDetail.setCName(rs3.getString(1));
//                    dbTableDetail.setCType(rs3.getString(2));
//                    dbTableDetail.setCPoint(rs3.getString(3));
//                    dbTableDetail.setCNull(rs3.getString(4));
//                    dbTableDetail.setCKey(rs3.getString(5));
//                    dbTableDetail.setCDetail(rs3.getString(6));
//                    dbTableDetail.setCCharacterSetName(rs3.getString(7));
//                    dbTableDetail.setCCollationName(rs3.getString(8));
//                    dbTableDetail.setCExtra(rs3.getString(9));
//                    dbTableDetail.setCDefault(rs3.getString(10));
//                    this.baseMapper.insertDbTableDetail(dbTableDetail);
//                }
//            }
            } catch (SQLException | ClassNotFoundException e) {
                System.out.println("获取表名失败");
            } finally {
                try {
                    rs.close();
                    rs1.close();
//                rs3.close();
                    conn.close();
                    conn1.close();
//                conn3.close();
                } catch (SQLException e) {
                    System.out.println("关闭连接失败");
                }
            }

            //注册申请的时候需要抓取数据
            //将申请的数据放到etl_job表中
            EtlJob etlJob = new EtlJob();
            Random random = new Random();
            //获取数据库中的表
            DbServicedetailsParam dbServicedetailsParam = new DbServicedetailsParam();
            dbServicedetailsParam.setServiceId(dbContentapprovalParam.getServiceid());
            List<DbServicedetailsResult> list2 = dbServicedetailsService.findListBySpec(dbServicedetailsParam);
            MetadataParam metadataParam = new MetadataParam();
            for (int i = 0; i < list2.size(); i++) {
                metadataParam.setTableName(list2.get(i).getTableName());
                List<MetadataResult> list3 = metadataService.findListBySpec(metadataParam);

                int y = 0;
                do {
                    y = random.nextInt(1000000000);
                    String x = "j" + String.valueOf(y);
                    etlJob.setJobId(x);
                }
                while (this.baseMapper.searchEtlJob(etlJob).size() > 0);
                etlJob.setMetadataId(String.valueOf(list3.get(0).getMetadataId()));
                etlJob.setToDsId("0");
                etlJob.setFromDsId(res.getId().toString());
                etlJob.setJobName(res.getSystem());
                this.baseMapper.insertEtlJob(etlJob);

                //获取到刚添加到etl_job表中的id
                List<EtlJob> listEtlJob = this.baseMapper.searchEtlJob(etlJob);
                //获取数据
                // 创建默认的httpClient实例.
                CloseableHttpClient httpclient = HttpClients.createDefault();
                // 创建httppost
                //TODO 部署的时候这个地方要改成调用迁数据项目的地址
                HttpPost httppost = new HttpPost("http://172.16.0.157:9000/ETLEngine/api/job/startJOb");
                // 创建参数队列
                List<BasicNameValuePair> formParams = new ArrayList<BasicNameValuePair>();
                formParams.add(new BasicNameValuePair("jobid", listEtlJob.get(0).getJobId()));
                formParams.add(new BasicNameValuePair("runType", "1"));
                UrlEncodedFormEntity uefEntity;
                try {
                    uefEntity = new UrlEncodedFormEntity(formParams, "UTF-8");
                    httppost.setEntity(uefEntity);
                    System.out.println("executing request " + httppost.getURI());
                    CloseableHttpResponse response = httpclient.execute(httppost);
                    try {
                        HttpEntity entity = response.getEntity();
                        if (entity != null) {
                            System.out.println("--------------------------------------");
                            System.out.println("Response content: " + EntityUtils.toString(entity, "UTF-8"));
                            System.out.println("--------------------------------------");
                        }
                    } finally {
                        response.close();
                    }
                } catch (ClientProtocolException e) {
                    e.printStackTrace();
                } catch (UnsupportedEncodingException e1) {
                    e1.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                } finally {
                    // 关闭连接,释放资源
                    try {
                        httpclient.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
            }




        }

        //如果是激活申请,需要连接目标数据库,恢复用户权限
//        Connection conn5 = null;
        if ("5".equals(dbContentapprovalParam.getContent())) {
            dbServicelist.setState("0");
            dbServicelist.setOpen("0");
//            try {
//                //加载驱动程序
//                Class.forName(driver);
//                //1.getConnection()方法，连接MySQL数据库！！
//                conn5 = DriverManager.getConnection(url, username, password);
//                //执行的sql获取该数据库下所有表
//                String sql = "grant all privileges on " + res.getDbName() + ".* to " + username + "@" + res.getDbUrl() + " identified by " + "\'" + password + "\'";
//                String sql1 = "flush privileges";
//                Statement stmt = conn5.createStatement();
//                stmt.executeUpdate(sql);
//                stmt.executeUpdate(sql1);
//            } catch (SQLException | ClassNotFoundException e) {
//                System.out.println("添加用户权限失败");
//            } finally {
//                try {
//                    conn5.close();
//                } catch (SQLException e) {
//                    System.out.println("关闭连接失败");
//                }
//            }

            //更改管理端数据库状态
            DblistParam dblistParam = new DblistParam();
            dblistParam.setServiceListId(dbContentapprovalParam.getServiceid());
            dblistParam.setState("1");
            dblistService.alterDbListInfoByServiceId(dblistParam);
        }
        if ("3".equals(dbContentapprovalParam.getContent())) {
            //删除数据库列表信息(设置数据库列表state为10)
            DbServicelistParam dbServicelistParam = new DbServicelistParam();
            dbServicelistParam.setId(dbContentapprovalParam.getServiceid());
            dbServicelistParam.setState("10");
            dbServicelistService.update(dbServicelistParam);
            //删除管理端数据库状态列表信息(设置管理端数据库状态表state为10)
            DblistParam dblistParam = new DblistParam();
            dblistParam.setServiceListId(dbContentapprovalParam.getServiceid());
            DblistResult dblistResult = dblistService.searchDbListInfoByServiceId(dblistParam);
            dblistParam.setId(dblistResult.getId());
            dblistParam.setState("10");
            dblistService.update(dblistParam);
            //取消线上数据库的所有权限
            //声明连接
//            Connection conn5 = null;
//            try {
//                //加载驱动程序
//                Class.forName(driver);
//                //1.getConnection()方法，连接MySQL数据库！！
//                conn5 = DriverManager.getConnection(url, username, password);
//                //执行的sql获取该数据库下所有表
//                String sql = "revoke all privileges on " + res.getDbName() + ".* from " + res.getUsername() + "@\"%\"";
//                String sql1 = "flush privileges";
//                Statement stmt = conn.createStatement();
//                stmt.executeUpdate(sql);
//                stmt.executeUpdate(sql1);
//            } catch (SQLException | ClassNotFoundException e) {
//                System.out.println("取消用户权限失败");
//            } finally {
//                try {
//                    conn5.close();
//                } catch (SQLException e) {
//                    System.out.println("关闭连接失败");
//                }
//            }

        }
        dbServicelistService.updateById(dbServicelist);


    }

    @Override
    public void onDown(DbContentapprovalParam dbContentapprovalParam) {
        DbContentapproval dbContentapproval = new DbContentapproval();
        dbContentapproval.setId(dbContentapprovalParam.getId());
        dbContentapproval.setState("2");//状态2是驳回
        dbContentapproval.setUpdatedTime(new Date());
        dbContentapproval.setUpdatedBy(LoginContextHolder.getContext().getUser().getUsername());
        baseMapper.updateById(dbContentapproval);
        DbServicelist dbServicelist = new DbServicelist();
        dbServicelist.setId(dbContentapprovalParam.getServiceid());
        dbServicelist.setSpState("2");
        dbServicelist.setUpdatedBy(LoginContextHolder.getContext().getUser().getUsername());
        dbServicelist.setUpdatedTime(new Date());
        dbServicelistService.updateById(dbServicelist);


        //审批驳回的时候放在通知中心一条消息更改其中的状态
        DbNoticeParam dbNoticeParam = new DbNoticeParam();
        dbNoticeParam.setSpState("3");
        dbNoticeParam.setState("0");
        dbNoticeParam.setAdminState("0");
        dbNoticeParam.setFirstIn("1");
        dbNoticeParam.setServiceId(dbContentapprovalParam.getServiceid());
        dbNoticeService.updateInfoByServiceId(dbNoticeParam);
    }

    private Serializable getKey(DbContentapprovalParam param) {
        return param.getId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private DbContentapproval getOldEntity(DbContentapprovalParam param) {
        return this.getById(getKey(param));
    }

    private DbContentapproval getEntity(DbContentapprovalParam param) {
        DbContentapproval entity = new DbContentapproval();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }

}
